package com.pro01.backend.mapper;

import com.pro01.backend.dto.PostDetailDTO;
import com.pro01.backend.dto.PostSummaryDTO;
import com.pro01.backend.pojo.Forum2Post;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface Forum2PostMapper {

    /**
     * 用户发帖
     * @param post
     * @return
     */
    @Insert("""
        INSERT INTO forum2_posts
        (section, user_id, title, content, is_pinned, status, view_count, comment_count, like_count, created_at, updated_at)
        VALUES
        (#{section}, #{userId}, #{title}, #{content}, false, 'ACTIVE', 0, 0, 0, NOW(), NOW())
        """)
    int insertPost(Forum2Post post); // ✅ 改为返回插入条数

    /**
     * 用户查看当前板块的帖子列表
     * @param section
     * @param offset
     * @param limit
     * @return
     */
    @Select("""
        SELECT
            p.id AS postId,
            p.section,
            p.title,
            p.user_id AS userId,
            u.username,
            u.title AS titleTag,
            u.nickname_color AS nicknameColor,
            u.avatar_frame AS avatarFrame,
            p.is_pinned,
            p.view_count,
            p.comment_count,
            p.like_count,
            p.created_at
        FROM forum2_posts p
        JOIN users u ON p.user_id = u.id
        WHERE p.section = #{section} AND p.status = 'ACTIVE'
        ORDER BY p.is_pinned DESC, p.created_at DESC
        LIMIT #{limit} OFFSET #{offset}
    """)
    List<PostSummaryDTO> selectPostsBySection(@Param("section") String section,
                                              @Param("offset") int offset,
                                              @Param("limit") int limit);

    @Select("""
        SELECT COUNT(*) FROM forum2_posts
        WHERE section = #{section} AND status = 'ACTIVE'
    """)
    int countPostsBySection(@Param("section") String section);

    /**
     * 用户查看帖子详情
     */
    @Select("""
    SELECT
        p.id AS postId,
        p.section,
        p.title,
        p.content,
        p.user_id AS userId,
        u.username,
        u.title AS titleTag,
        u.nickname_color AS nicknameColor,
        u.avatar_frame AS avatarFrame,
        p.is_pinned,
        p.view_count,
        p.comment_count,
        p.like_count,
        p.created_at
    FROM forum2_posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.id = #{postId} AND p.status = 'ACTIVE'
    """)
    PostDetailDTO selectPostDetail(@Param("postId") Long postId);

    // 浏览量 +1
    @Update("UPDATE forum2_posts SET view_count = view_count + 1 WHERE id = #{postId}")
    void incrementViewCount(@Param("postId") Long postId);

    /**
     * 用户删除帖子
     */
    @Update("""
    UPDATE forum2_posts
    SET status = 'DELETED'
    WHERE id = #{postId}
    """)
    int markPostAsDeleted(@Param("postId") Long postId);

    @Select("SELECT user_id FROM forum2_posts WHERE id = #{postId}")
    Long getPostOwnerId(@Param("postId") Long postId);

    /**
     * 用户隐藏/显示帖子
     */
    @Update("""
    UPDATE forum2_posts
    SET status = #{status}
    WHERE id = #{postId}
    """)
    int updatePostStatus(@Param("postId") Long postId, @Param("status") String status);

    /**
     * 用户模糊查询帖子
     */
    @Select("""
    SELECT
        p.id AS postId,
        p.section,
        p.title,
        p.user_id AS userId,
        u.username,
        u.title AS titleTag,
        u.nickname_color AS nicknameColor,
        u.avatar_frame AS avatarFrame,
        p.is_pinned,
        p.view_count,
        p.comment_count,
        p.like_count,
        p.created_at
    FROM forum2_posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.status = 'ACTIVE'
      AND (p.title LIKE CONCAT('%', #{keyword}, '%') OR p.content LIKE CONCAT('%', #{keyword}, '%'))
    ORDER BY p.is_pinned DESC, p.created_at DESC
    LIMIT #{limit} OFFSET #{offset}
    """)
    List<PostSummaryDTO> searchPostsByKeyword(@Param("keyword") String keyword,
                                              @Param("offset") int offset,
                                              @Param("limit") int limit);

    @Select("""
    SELECT COUNT(*) FROM forum2_posts
    WHERE status = 'ACTIVE'
      AND (title LIKE CONCAT('%', #{keyword}, '%') OR content LIKE CONCAT('%', #{keyword}, '%'))
    """)
    int countSearchPosts(@Param("keyword") String keyword);

    /**
     * 管理员查看所有帖子列表
     */
    @Select("""
    SELECT
        p.id AS postId,
        p.section,
        p.title,
        p.user_id AS userId,
        u.username,
        u.title AS titleTag,
        u.nickname_color AS nicknameColor,
        u.avatar_frame AS avatarFrame,
        p.is_pinned,
        p.view_count,
        p.comment_count,
        p.like_count,
        p.status,
        p.created_at
    FROM forum2_posts p
    JOIN users u ON p.user_id = u.id
    ORDER BY p.created_at DESC
    LIMIT #{limit} OFFSET #{offset}
""")
    List<PostSummaryDTO> selectAllPostsPaged(@Param("offset") int offset,
                                             @Param("limit") int limit);

    @Select("SELECT COUNT(*) FROM forum2_posts")
    int countAllPosts();

    /**
     * 置顶/取消置顶
     */
    @Update("""
    UPDATE forum2_posts
    SET is_pinned = #{pinned}
    WHERE id = #{postId}
""")
    int updatePostPinned(@Param("postId") Long postId, @Param("pinned") boolean pinned);

    // 推荐系统
//    @Select("""
//    SELECT *
//    FROM (
//        SELECT
//            p.id AS postId,
//            p.section,
//            p.title,
//            p.user_id AS userId,
//            u.username,
//            u.title AS titleTag,
//            u.nickname_color AS nicknameColor,
//            u.avatar_frame AS avatarFrame,
//            u.level,
//            p.is_pinned,
//            p.view_count,
//            p.comment_count,
//            p.like_count,
//            p.status,
//            p.created_at,
//            (p.view_count * 1 + p.comment_count * 2 + p.like_count * 6) AS score
//        FROM forum2_posts p
//        JOIN users u ON p.user_id = u.id
//        WHERE p.status = 'ACTIVE'
//    ) AS ranked
//    WHERE (ranked.section, ranked.score, ranked.created_at) IN (
//        SELECT
//            section,
//            MAX(score) AS max_score,
//            MAX(created_at) AS max_time
//        FROM (
//            SELECT
//                section,
//                (view_count * 1 + comment_count * 2 + like_count * 6) AS score,
//                created_at
//            FROM forum2_posts
//            WHERE status = 'ACTIVE'
//        ) AS scored
//        GROUP BY section
//    )
//""")
//    List<PostSummaryDTO> selectRecommendedPostPerSection();
    @Select("""
    SELECT * FROM (
        SELECT
            p.id AS postId,
            p.section,
            p.title,
            p.user_id AS userId,
            u.username,
            u.title AS titleTag,
            u.nickname_color AS nicknameColor,
            u.avatar_frame AS avatarFrame,
            u.level,
            p.is_pinned,
            p.view_count,
            p.comment_count,
            p.like_count,
            p.status,
            p.created_at,
            (p.view_count * 1 + p.comment_count * 2 + p.like_count * 6) AS score,
            ROW_NUMBER() OVER (
                PARTITION BY p.section
                ORDER BY (p.view_count * 1 + p.comment_count * 2 + p.like_count * 6) DESC, p.created_at DESC
            ) AS rn
        FROM forum2_posts p
        JOIN users u ON p.user_id = u.id
        WHERE p.status = 'ACTIVE'
    ) ranked
    WHERE rn = 1
""")
    List<PostSummaryDTO> selectRecommendedPostPerSection();

}
